package com.mobi.Survey.lib.db;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.mobi.Survey.lib.model.Address;
import com.mobi.Survey.lib.model.Pojo;
import com.mobi.Survey.lib.model.SubscriptionPojo;
import com.mobi.Survey.lib.model.SurveyPojo;
import com.mobi.Survey.lib.utils.Helper;

/**
 * 
 * <p>
 * <b>Author:</b> Yahya Arshad
 * <p>
 * <b>Company:</b> Mobi Media Soft
 * <p>
 * <b> Website:</b> www.mobimediasoft.com
 * <p>
 * SurveyTable.java This class provides interfaces for operation on Survey Table
 * 
 */
public class SurveyTable extends AbstractTable {

	public static final String TABLE = "survey";

	public static final String COL_SURVEY_ID = "_Id";
	public static final String COL_SURVEY_KEY = "survey_key";
	public static final String COL_SURVEY_DESC = "description";
	public static final String COL_SUBS_ID = "subsid";
	public static final String COL_COMPANY_NAME = "company_name";
	public static final String COL_WEB_SITE = "web_site";
	public static final String COL_PHONE = "phone";
	public static final String COL_EMAIL = "email";
	public static final String COL_PAGE = "page";
	/**
	 * will be used for Information button as we don't have start button now(CFR
	 * - 4)
	 */
	public static final String COL_START_SURVEY_BTN_VAL = "txt_start_survey";
	public static final String COL_VIEW_CORRECT_ANSWER_BTN_VAL = "txt_correct_answer";
	public static final String COL_VIEW_RESULT_BTN_VAL = "txt_survey_result";
	public static final String COL_TIMER_BTN_VAL = "txt_timer_btn";
	public static final String COL_ICON_ADDRESS = "icon_url";
	public static final String COL_BG_COLOR = "bg_color";
	public static final String COL_FONT_COLOR = "font_color";
	public static final String COL_BG_IMAGE_URL = "bg_image_url";
	
	public static final String COL_USER_ID = "user_id";
	

	@Override
	public long insertRow(Pojo obj) {

		SurveyPojo survey = (SurveyPojo) obj;

		if (survey.getId() <= 0 || survey.getSurveyKey().length() == 0)
			return -1;
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		ContentValues cv = new ContentValues();
		cv.put(COL_SURVEY_ID, survey.getId());
		cv.put(COL_SURVEY_KEY, survey.getSurveyKey().toUpperCase());
		cv.put(COL_SURVEY_DESC, survey.getSurveyDiscription());

		cv.put(COL_SUBS_ID, survey.getSubscriptionId());
		cv.put(COL_COMPANY_NAME, survey.getCompanyName());
		cv.put(COL_WEB_SITE, survey.getWebsite());
		cv.put(COL_PHONE, survey.getPhone());
		cv.put(COL_EMAIL, survey.getContactEmail());

		cv.put(COL_PAGE, survey.isShowAllControls());
		cv.put(COL_START_SURVEY_BTN_VAL, survey.getTxtButtonInformation());
		cv.put(COL_VIEW_CORRECT_ANSWER_BTN_VAL, survey.getTxtBtnCorrectAnswer());
		cv.put(COL_VIEW_RESULT_BTN_VAL, survey.getTxtBtnSurveyResult());
		cv.put(COL_TIMER_BTN_VAL, survey.getTxtButtonTimer());
		cv.put(COL_ICON_ADDRESS, survey.getIconAddress());
		cv.put(COL_BG_COLOR, survey.getbGColor());
		cv.put(COL_BG_IMAGE_URL, survey.getbGImageUrl());
		cv.put(COL_FONT_COLOR, survey.getFontColor());
		cv.put(COL_USER_ID, survey.getUserid());

		if (db.insert(TABLE, null, cv) > 0)
			return survey.getId();
		else {
			return -1;
		}

	}

	public SurveyPojo getSurveyForAnswer(Context ctx, long surveyId) {

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, COL_SURVEY_ID + "=? ",
				new String[] { surveyId + "" }, null, null, null);
		SurveyPojo survey = new SurveyPojo();

		if (cursor != null) {

			if (cursor.moveToFirst()) {

				long id = cursor.getLong(0);
				String key = cursor.getString(1);
				String description = cursor.getString(2);
				int subsid = cursor.getInt(3);

				boolean showAll = cursor.getInt(8) > 0;

				String bgColor = cursor.getString(13);
				String bgImageUrl = cursor.getString(14);
				String fontColor = cursor.getString(15);
				
				survey.setId(id);
				survey.setShowAllControls(showAll);
				survey.setSubscriptionId(subsid);
				survey.setSurveyDiscription(description);
				survey.setSurveyKey(key);
				survey.setbGColor(bgColor);
				survey.setbGImageUrl(bgImageUrl);
				survey.setFontColor(fontColor);

				SurveyDetailTable detailTable = new SurveyDetailTable(ctx);

				survey.setSurveyDetail(detailTable.getSurveyDetail(id));

				detailTable.closeDB();
			}

			cursor.close();
		}

		return survey;

	}

	public SurveyPojo getSurveyWithDetail(Context ctx, long surveyId) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, COL_SURVEY_ID + "=? ",
				new String[] { surveyId + "" }, null, null, null);
		SurveyPojo survey = new SurveyPojo();

		if (cursor != null) {
			if (cursor.moveToFirst()) {

				long id = cursor.getLong(0);
				String key = cursor.getString(1);
				String description = cursor.getString(2);
				int subsid = cursor.getInt(3);
 
				boolean isSingle = cursor.getInt(8) > 0;
 
				String bgColor = cursor.getString(13);
				String bgImageUrl = cursor.getString(14);
				String fontColor = cursor.getString(15);
				survey.setId(id);
 
				survey.setShowAllControls(isSingle);
				survey.setSubscriptionId(subsid);
				survey.setSurveyDiscription(description);
				survey.setSurveyKey(key);
 
				survey.setbGColor(bgColor);
				survey.setbGImageUrl(bgImageUrl);
				survey.setFontColor(fontColor);

				AbstractTable ab = new SurveyDetailTable(ctx);

				survey.setSurveyDetail(((SurveyDetailTable) ab).getSurveyDetail(id));

				ab.closeDB();
			}
			cursor.close();
		}

		return survey;
	}

	public SurveyPojo getSurveyForInformationScreen(Context ctx, long surveyId) {

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, COL_SURVEY_ID + "=? ",
				new String[] { surveyId + "" }, null, null, null);
		SurveyPojo survey = new SurveyPojo();

		if (cursor != null) {
			if (cursor.moveToFirst()) {

				long id = cursor.getLong(0);
				String key = cursor.getString(1);
				String description = cursor.getString(2);
				int subsid = cursor.getInt(3);
				String company = cursor.getString(4);
				String website = cursor.getString(5);
				String phone = cursor.getString(6);
				String email = cursor.getString(7);
 
				survey.setId(id);

				survey.setCompanyName(company);
				survey.setContactEmail(email);
 
				survey.setSubscriptionId(subsid);
				survey.setSurveyDiscription(description);
				survey.setSurveyKey(key);
				survey.setWebsite(website);

				survey.setPhone(phone);
				SubscriptionTable subscriptionTable = new SubscriptionTable(ctx);
				survey.setSubscription(subscriptionTable.getSubscriberData(subsid));
				subscriptionTable.closeDB();

				AddressTable addressTable = new AddressTable(ctx);
				Address address = addressTable.getAddressBySurveyId(surveyId);
				survey.setAddress(address);
				addressTable.closeDB();
 
			}
			cursor.close();
		}

		return survey;

	}

	public List<SurveyPojo> getSurveysForMapScreen(Context ctx) {

		List<SurveyPojo> surveys = new ArrayList<SurveyPojo>();

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, null, null, null, null, null);

		if (null != cursor) {
			if (cursor.moveToFirst()) {

				SubscriptionTable subscriptionTable = new SubscriptionTable(ctx);
				AddressTable addressTable = new AddressTable(ctx);
				SurveyResultTable resultTable = new SurveyResultTable(ctx);
				do {
					SurveyPojo survey = new SurveyPojo();
					long id = cursor.getLong(0);
					String key = cursor.getString(1);
					String description = cursor.getString(2);
					int subsid = cursor.getInt(3);

					Address address = addressTable.getAddressBySurveyId(id);
					if (address.isAddressNull()
							|| address.getCoordinates() == null) {
						continue;
					}

					String company = cursor.getString(4);
					String website = cursor.getString(5);
					String phone = cursor.getString(6);
					String email = cursor.getString(7);
					boolean isSingle = cursor.getInt(8) > 0;
					String txtStartSurvey = cursor.getString(9);
					String txtCorrectAnswer = cursor.getString(10);
					String txtResultVals = cursor.getString(11);
					String iconAddress = cursor.getString(12);
					String bgColor = cursor.getString(13);
					String bgImageUrl = cursor.getString(14);
					String fontColor = cursor.getString(15);
					String txtButtonTimer = cursor.getString(16);

					survey.setId(id);
					survey.setCompanyName(company);
					survey.setContactEmail(email);
					survey.setShowAllControls(isSingle);
					survey.setSubscriptionId(subsid);
					survey.setSurveyDiscription(description);
					survey.setSurveyKey(key);
					survey.setWebsite(website);

					survey.setPhone(phone);
					survey.settxtButtonInformation(txtStartSurvey);
					survey.setTxtBtnCorrectAnswer(txtCorrectAnswer);
					survey.setTxtBtnSurveyResult(txtResultVals);
					survey.setbGColor(bgColor);
					survey.setbGImageUrl(bgImageUrl);
					survey.setFontColor(fontColor);
					survey.setTxtButtonTimer(txtButtonTimer);

					survey.setAddress(address);

					if (iconAddress != null && iconAddress.length() > 0) {

						String file = Helper.getPathToAppFolder()
								+ Helper.getHashNameFromUrl(iconAddress);

						survey.setBitmapIcon(Helper.decode70x70(file, 1));
					}

					SubscriptionPojo sub = subscriptionTable.getSubscriberData(subsid);

					survey.setSubscription(sub);
					survey.setAnswered(resultTable.isNew(id) > 0);

					surveys.add(survey);

				} while (cursor.moveToNext());

				subscriptionTable.closeDB();
				addressTable.closeDB();
				resultTable.closeDB();

			}
			cursor.close();
			 
		}

		return surveys;

	}

	public List<SurveyPojo> getSurveysWithSubscriptions(Context ctx) {

		List<SurveyPojo> surveys = new ArrayList<SurveyPojo>();

		SQLiteDatabase db = dbHelper.getReadableDatabase();

		Cursor cursor = db.query(TABLE, null, null, null, null, null, null);

		if (null != cursor) {
			if (cursor.moveToFirst()) {

				SubscriptionTable subscriptionTable = new SubscriptionTable(ctx);
				SurveyResultTable resultTable = new SurveyResultTable(ctx);
		 
				do {
					SurveyPojo survey = new SurveyPojo();
					long survyeId = cursor.getLong(0);
					String key = cursor.getString(1);
					String description = cursor.getString(2);
					int subsid = cursor.getInt(3);
					String company = cursor.getString(4);
					String website = cursor.getString(5);
					String phone = cursor.getString(6);
					String email = cursor.getString(7);
					boolean isSingle = cursor.getInt(8) > 0;
					String txtStartSurvey = cursor.getString(9);
					String txtCorrectAnswer = cursor.getString(10);
					String txtResultVals = cursor.getString(11);
					String iconAddress = cursor.getString(12);
					String bgColor = cursor.getString(13);
					String bgImageUrl = cursor.getString(14);
					String fontColor = cursor.getString(15);
					String txtButtonTimer = cursor.getString(16);

					survey.setId(survyeId);
					survey.setCompanyName(company);
					survey.setContactEmail(email);
					survey.setShowAllControls(isSingle);
					survey.setSubscriptionId(subsid);
					survey.setSurveyDiscription(description);
					survey.setSurveyKey(key);
					survey.setWebsite(website);
					survey.setPhone(phone);
					survey.settxtButtonInformation(txtStartSurvey);
					survey.setTxtBtnCorrectAnswer(txtCorrectAnswer);
					survey.setTxtBtnSurveyResult(txtResultVals);
					survey.setbGColor(bgColor);
					survey.setbGImageUrl(bgImageUrl);
					survey.setFontColor(fontColor);
					survey.setTxtButtonTimer(txtButtonTimer);

					if (iconAddress != null && iconAddress.length() > 0) {

						String file = Helper.getPathToAppFolder()
								+ Helper.getHashNameFromUrl(iconAddress);

						survey.setBitmapIcon(Helper.decode70x70(file, 1));
					}

					survey.setSubscription(subscriptionTable.getSubscriberData(subsid));
 
					survey.setAnswered(resultTable.isNew(survyeId) > 0);

					surveys.add(survey);

				} while (cursor.moveToNext());

				subscriptionTable.closeDB();
				resultTable.closeDB();
		 

			}

			cursor.close();

		}
	 
		return surveys;

	}

	/**
	 * 
	 * 
	 * @param surveyKey
	 *            Survey Key
	 * @return id of the survey if found
	 */

	public int isNew(String surveyKey) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { COL_SURVEY_ID },
				COL_SURVEY_KEY + " Like '" + surveyKey + "' ", null, null,
				null, null);
		int id = -1;
		if (null != cursor) {

			if (cursor.moveToFirst()) {
				id = cursor.getInt(0);
			}
			cursor.close();
		}
		return id;

	}

	public SurveyTable() {

	}

	/**
	 * 
	 * @param sId
	 *            Survey Id
	 * @return Survey Key of given id
	 */
	public String getSurveyKeyById(long sId) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { COL_SURVEY_KEY },
				COL_SURVEY_ID + " = " + sId, null, null, null, null);

		String sKey = null;
		if (cursor != null) {

			
			if (cursor.moveToFirst()) {
				sKey = cursor.getString(0);
			}
 
			cursor.close();
			cursor = null;
		}
		return sKey;
	}

	/**
	 * 
	 * @param sKey
	 *            Survey Key
	 * @return Survey id of geven key
	 */
	public int getSurveyIdByKey(String sKey) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { COL_SURVEY_ID },
				COL_SURVEY_KEY + " Like '" + sKey + "' ", null, null, null,
				null);

		int id = -1;
		if (cursor != null) {
 
			if (cursor.moveToFirst()) {
				id = cursor.getInt(0);
			}

			cursor.close();
			cursor = null;
		}
		return id;

	}

	public SurveyTable(Context ctx) {
		super(ctx);
	}

	/**
	 * 
	 * @return total surveys in database
	 */
	public int getSurveyCount() {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { "COUNT("+COL_SURVEY_KEY+") " }, null,
				null, null, null, null);
		int total = -1;
		if (cursor != null) {
 
			if (cursor.moveToFirst()) {
				total = cursor.getInt(0);
			}

			cursor.close();
			cursor = null;
		}
		return total;
	}
	
	
	public int getSurveyCountByUserId(String userId) {
		SQLiteDatabase db = dbHelper.getReadableDatabase();
		Cursor cursor = db.query(TABLE, new String[] { "COUNT("+COL_USER_ID+") " }, COL_USER_ID  +" = ? ",
				new String[]{userId}, null, null, null);
		int total = -1;
		if (cursor != null) {
 
			if (cursor.moveToFirst()) {
				total = cursor.getInt(0);
			}

			cursor.close();
			cursor = null;
		}
		return total;
	}

	@Override
	public boolean createTable(SQLiteDatabase sqliteDb) {

		String query = "create table " + TABLE + "( " + COL_SURVEY_ID
				+ " BIGINT primary key,  " + COL_SURVEY_KEY
				+ " text unique not null, " + COL_SURVEY_DESC + " text, "
				+ COL_SUBS_ID + " long, " + COL_COMPANY_NAME + " text, "
				+ COL_WEB_SITE + " text, " + COL_PHONE + " text, " + COL_EMAIL
				+ " text, " + COL_PAGE + " text, " + COL_START_SURVEY_BTN_VAL
				+ " text, " + COL_VIEW_CORRECT_ANSWER_BTN_VAL + " text, "
				+ COL_VIEW_RESULT_BTN_VAL + " text, " +COL_ICON_ADDRESS
				+ " text, " + COL_BG_COLOR + " text, " + COL_BG_IMAGE_URL
				+ " text, " + COL_FONT_COLOR + " text, "+ COL_TIMER_BTN_VAL +" text, "+COL_USER_ID+" text )";

		sqliteDb.execSQL(query);
		printMessage(query);
		return true;
	}

	/**
	 * 
	 * @param id
	 *            Id of Survey to be updated
	 * @param survey
	 *            Pojo of survey for update
	 * @return true if update successfull
	 */
	public boolean updateRow(long id, SurveyPojo survey) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();

		ContentValues cv = new ContentValues();

		// cv.put(COL_SURVEY_KEY, survey.getSurveyKey());
		cv.put(COL_SURVEY_DESC, survey.getSurveyDiscription());

		cv.put(COL_SUBS_ID, survey.getSubscriptionId());

		cv.put(COL_COMPANY_NAME, survey.getCompanyName());

		cv.put(COL_WEB_SITE, survey.getWebsite());
		cv.put(COL_PHONE, survey.getPhone());

		cv.put(COL_EMAIL, survey.getContactEmail());

		cv.put(COL_PAGE, survey.isShowAllControls());
		cv.put(COL_START_SURVEY_BTN_VAL, survey.getTxtButtonInformation());
		cv.put(COL_VIEW_CORRECT_ANSWER_BTN_VAL, survey.getTxtBtnCorrectAnswer());
		cv.put(COL_VIEW_RESULT_BTN_VAL, survey.getTxtBtnSurveyResult());
		cv.put(COL_BG_COLOR, survey.getbGColor());
		cv.put(COL_BG_IMAGE_URL, survey.getbGImageUrl());
		cv.put(COL_FONT_COLOR, survey.getFontColor());
		 
		int ret = db.update(TABLE, cv, COL_SURVEY_ID + " = ? ",
				new String[] { id + "" });
		cv.clear();

		return ret > 0;

	}

	@Override
	public boolean deleteRow(long id) {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		int effected = db.delete(TABLE, COL_SURVEY_ID + " = ?",
				new String[] { id + "" });
		return effected > 0;
	}

	@Override
	public void deleteAllData() {
		SQLiteDatabase db = dbHelper.getWritableDatabase();
		db.delete(TABLE, null, null);

	}

}
